const dayjs = require("dayjs");
const XLSX = require("xlsx");
// 解析 multipart form-data 格式的请求
const multiparty = require("multiparty");
const fs = require("fs");
// 添加用户数据处理函数
async function addUserFun(res, databaseValues, excelRows) {
  // 过滤出新增数据
  const filteredRows = excelRows.filter(
    (excelRow) =>
      !databaseValues.find(
        (databaseRow) => databaseRow.email === excelRow.email
      )
  );
  // 判断如何无数据可更新的情况
  if (filteredRows === null || filteredRows.length === 0) {
    // next();
    return res.cc("暂无数据可更新");
  }

  // 如果需要，可以将去重后的数据插入数据库
  const insertDataResult = filteredRows.map((row) => {
    const columns = Object.keys(row);
    const values = Object.values(row);
    const sql = `INSERT INTO fa_users (${columns.join(
      ", "
    )}) VALUES ('${values.join("', '")}')`;
    return global.query(sql);
  });

  Promise.all(insertDataResult)
    .then(async (r) => {
      res.cc("初始化用户" + r.length + "条信息成功", 200);
    })
    .catch((err) => {
      res.cc("插入失败");
    });
}
// 删除用户处理函数
function deleteUserFun(res, databaseValues, excelRows) {
  if (databaseValues.length > 0) {
    const filteredRows = excelRows.filter((excelRow) =>
      databaseValues.find((databaseRow) => databaseRow.email === excelRow.email)
    );
    const deleteDataResult = filteredRows.map((row) => {
      const values = Object.values(row);
      const emailVal = values[1].toString();
      const del_sql = `DELETE FROM fa_users  WHERE  email = ?`;
      return global.query(del_sql, [emailVal]);
    });
    Promise.all(deleteDataResult)
      .then((r) => {
        res.cc("删除" + r.length + "条成功", 200);
      })
      .catch((err) => {
        res.cc("删除失败");
      });
  } else {
    return res.cc("用户不存在，无法删除");
  }
}
// 修改队伍id和队伍名处理函数
function updateUserFun(res, databaseValues, excelRows) {
  if (databaseValues.length > 0) {
    const filteredRows = excelRows.filter((excelRow) =>
      databaseValues.find((databaseRow) => databaseRow.email === excelRow.email)
    );
    const upadateDataResult = filteredRows.map((row) => {
      const values = Object.values(row);
      const emailVal = values[1].toString();
      const update_sql = `UPDATE fa_users SET team_id  = ?, team_name = ?, code=? WHERE email = ?`;
      return global.query(update_sql, [
        values[2].toString(),
        values[3].toString(),
        values[4].toString(),
        emailVal,
      ]);
    });
    Promise.all(upadateDataResult)
      .then((r) => {
        res.cc("修改成功", 200);
      })
      .catch((err) => {
        res.cc(err);
      });
  } else {
    return res.cc("暂无修改数据");
  }
}
// 生成team表的处理模块
async function addTeamFun(res, excelRows) {
  const created_time = dayjs().format("YYYY-MM-DD HH:mm:ss");
  const filteredRows = excelRows.filter((obj, index) => {
    return (
      excelRows.findIndex((item) => item.team_id === obj.team_id) === index
    );
  });
  // console.log("filteredRows", filteredRows)
  // 清空team表数据
  // if (filteredRows.length > 0) {
  //   const delTable = `TRUNCATE TABLE fa_team`;
  //   await global.query(delTable);
  // }
  // 循环插入team表数据
  const insertTeamDataResult = filteredRows.map((row) => {
    const columns = Object.keys(row);
    const values = Object.values(row);
    const sql = `INSERT INTO fa_team (
      ${columns[2].toString()},
      ${columns[3].toString()},
      ${columns[4].toString()},
      nick_name,
      created_time
    ) VALUES (?,?,?,?,?)`;
    return global.query(sql, [
      values[2],
      values[3].toString(),
      values[4].toString(),
      values[3].toString(),
      created_time,
    ]);
  });
  Promise.all(insertTeamDataResult)
    .then((sqlRes) => {
      // next()
    })
    .catch((err) => {
      console.log("err---", err);
      res.cc("team插入失败");
    });
}
// 插入team表生成队伍分数字段的处理方法
function addToTeamScoreFun(res, excelRows) {
  const updated_time = dayjs().format("YYYY-MM-DD HH:mm:ss");
  const filteredRows = excelRows.filter((obj, index) => {
    return (
      excelRows.findIndex((item) => item.team_id === obj.team_id) === index
    );
  });
  // 循环插入team表数据分数
  const updateToTeamScoreDataResult = filteredRows.map((row) => {
    const columns = Object.keys(row);
    const values = Object.values(row);
    const update_sql = `update fa_team set score = ? , updated_time =? where team_id = ?`;
    return global.query(update_sql, [values[1], updated_time, values[0]]);
  });
  Promise.all(updateToTeamScoreDataResult)
    .then((sqlRes) => {
      console.log("sqlRes---", sqlRes);
      // if (sqlRes.affectedRows !== 1) {
      //   return res.cc("team分数插入失败,请检查队伍是否存在");
      // }
      res.cc("team分数" + sqlRes.length + "条数插入成功");
    })
    .catch((err) => {
      return res.cc("team分数插入失败");
    });
}
// 上传JSON文件处理函数
function uploadJsonFun(req, res, JsonData) {
  // 将上传的文件内容转换为字符串，然后解析为JSON
  // const jsonString = req.file.buffer.toString("utf8");
  // const jsonData = JSON.parse(jsonString);

  // // 这里可以处理jsonData，比如保存到数据库或进行其他逻辑处理
  // console.log(jsonData);
  res.cc("okoko");
}
// 上传Excel文件处理函数
exports.uploadFile = (req, res, next) => {
  try {
    const form = new multiparty.Form();
    // 上传图片需要保存在一个目录下面，目录必须存在
    // form.uploadDir = "./public/upload";
    form.parse(req, (err, fields, files) => {
      console.log("fields---", fields, files.file[0].headers);
      if (err) {
        global.logger.error({
          code: 500,
          msg: "invalid request: " + err.message,
        });
        return res.cc("invalid request: " + err.message);
      }
      if (
        !files.file[0].headers["content-type"] ==
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
      ) {
        return res.cc("文件类型错误！");
      }
      // 读取Excel文件
      const fileName = files.file[0].originalFilename;
      const fileUrl = files.file[0].path;
      const workbook = XLSX.readFile(fileUrl);
      const sheet = workbook.Sheets[workbook.SheetNames[0]];
      const excelData = XLSX.utils.sheet_to_json(sheet);
      console.log("excelData----", excelData);
      // 转换数据为数组，便于操作
      const excelRows = Array.isArray(excelData) ? excelData : [excelData];
      // 准备SQL查询库里的数据
      const uniqueCheckQuery = "SELECT * FROM fa_users WHERE email IN (?)";
      const valuesToCheck = excelRows.map((row) => row.email);
      // 根据is_create参数，生成team表
      if (Number(fields.type) == 1 && Number(fields.is_create) == 1) {
        addTeamFun(res, excelRows);
      }
      global.query(uniqueCheckQuery, [valuesToCheck]).then((results) => {
        //数据库现有的数据
        const databaseValues = results;
        // type 1新增数据 2删除数据 3修改数据
        if (Number(fields.type) === 1) {
          addUserFun(res, databaseValues, excelRows);
          //根据is_create是否创建team表 1 创建
        } else if (Number(fields.type) === 2) {
          // 查询数据库是否存在这条数据
          deleteUserFun(res, databaseValues, excelRows);
          // 修改用户表
        } else if (Number(fields.type) === 3) {
          updateUserFun(res, databaseValues, excelRows);
        } else if (Number(fields.type) === 4) {
          // 插入team表中不同队对应的分数字段
          addToTeamScoreFun(res, excelRows);
        } else if (Number(fields.type) === 5) {
          //  保存JSON文件内容在Redis服务上
          const excelDataToJson = JSON.stringify(excelRows);
          const fileName = "jsonFile";
          global.connectRedis.set(
            fileName,
            excelDataToJson,
            "EX",
            10 * 24 * 60 * 60
          );
          res.send({ code: 200, msg: "保存成功", data: excelRows });
        }
      });
    });
  } catch (error) {
    global.logger.error({
      code: 500,
      msg: "invalid request: " + error.message,
    });
    res.cc(error);
  }
};
